package com.gjy.easyexcel;

import com.gjy.oracle.OracleUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author gjy
 * @version 1.0
 * @since 2024-12-14 09:31:51
 */
public class ExportOracleData {

    public static List<List<Object>> exportList(String tableName) throws Exception {
        List<TableMsg> list = getTableMsgList(tableName);
        String collect = list.stream().map(TableMsg::getCOLUMN_NAME).collect(Collectors.joining(","));

        String sql = "select " + collect + " from " + tableName;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection connection = OracleUtil.getConnection();
        List<List<Object>> result = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                List<Object> row = new ArrayList<>();
                for (int i = 0; i < columnCount; i++) {
                    row.add(rs.getObject(metaData.getColumnLabel(i + 1)));
                }
                result.add(row);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            OracleUtil.close(connection, ps, rs);
        }
        return result;
    }

    public static List<TableMsg> getTableMsgList(String tableName) throws Exception {
        String sql = "select ATC.COLUMN_NAME, ATC.DATA_TYPE, ACC.COMMENTS\n" +
                "from ALL_TAB_COLUMNS ATC\n" +
                "         left join ALL_COL_COMMENTS ACC on ATC.TABLE_NAME = ACC.TABLE_NAME and ATC.COLUMN_NAME = ACC.COLUMN_NAME\n" +
                "where ATC.TABLE_NAME = ?\n" +
                "order by ATC.COLUMN_NAME";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection connection = OracleUtil.getConnection();
        List<TableMsg> list = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                TableMsg tableMsg = new TableMsg();
                tableMsg.setCOLUMN_NAME(rs.getString("COLUMN_NAME"));
                tableMsg.setDATA_TYPE(rs.getString("DATA_TYPE"));
                tableMsg.setCOMMENTS(rs.getString("COMMENTS"));
                list.add(tableMsg);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            OracleUtil.close(connection, ps, rs);
        }
        return list;
    }

}
